#!/bin/bash
if [ -n "$1" ] ; then
  data_str=$1
else
  data_str=`date -d "-1 days" +%F`
fi
SQL_DIM_MD_AREAS="
DROP FUNCTION IF EXISTS TO_GEOHASH;
CREATE FUNCTION TO_GEOHASH
    AS 'udf.ConvertGeoHash' USING JAR 'hdfs://node101:8020/warehouse/app_jars/zg6-jtp-log-1.0.jar';

SELECT TO_GEOHASH(35.46847397820407, 119.36278007513695, 6) AS da;
WITH tmp_area AS (SELECT ID, AREANAME, PARENTID, BD09_LNG, BD09_LAT
                  FROM jtp_app_ods.ods_md_areas
                  WHERE dt = '${data_str}'
                    AND BD09_LAT IS NOT NULL
                    AND BD09_LNG IS NOT NULL),
     tmp1 AS (SELECT ID, AREANAME AS province_name
              FROM tmp_area
              WHERE PARENTID = 0),
     tmp2 AS (SELECT t2.ID, t1.province_name, t2.AREANAME AS city_name
              FROM tmp1 t1
                       LEFT JOIN tmp_area t2 ON t1.ID = t2.PARENTID),
     tmp3 AS (SELECT t2.ID, t1.province_name, t1.city_name, t2.AREANAME AS district_name
              FROM tmp2 t1
                       LEFT JOIN tmp_area t2 ON t1.ID = t2.PARENTID)
INSERT INTO jtp_app_dim.dim_md_areas PARTITION (dt='${data_str}')
SELECT t2.ID, t1.province_name, t1.city_name, t1.district_name, t2.AREANAME AS street_name,
       t2.BD09_LNG,t2.BD09_LAT,TO_GEOHASH(t2.BD09_LAT,t2.BD09_LNG,6)
FROM tmp3 t1
         LEFT JOIN tmp_area t2 ON t1.ID = t2.PARENTID WHERE BD09_LAT IS NOT NULL AND BD09_LNG IS NOT NULL
;
"


/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${SQL_DIM_MD_AREAS}"
